package dao;

import model.Blog;
import utills.DBUtils;
import utills.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author quan
 * @Description 查询Blog表中的所有记录
 * @Date 2023/7/4 19:16
 */
public class BlogDao {
    /**
     * 获取所有的文章列表
     * @return
     */
    public List<Blog> selectAll(){
        //1、定义数据库访问的相关对象
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //2、建立数据库连接
        try {
            connection = DBUtils.getConnection();
            //3、定义sql语句:按发布时间排序
            String sql = "select id,title,content,createTime,userId " +
                    "from blog order by createTime desc";
            //4、处理sql
            statement = connection.prepareStatement(sql);
            //5、执行sql语句，获取结果集
            resultSet = statement.executeQuery();
            //6、遍历结果集
            List<Blog> blogList = null;
            while (resultSet.next()){
                //为空的时候初始化一个集合
                if(blogList == null){
                    blogList = new ArrayList<Blog>();
                }
                //集合中的每个元素都是一个blog对象
                Blog blog = new Blog();
                blog.setId(resultSet.getLong(1));
                blog.setTitle(resultSet.getString(2));
                blog.setContent(resultSet.getString(3));
                //判断一下内容长度，如果内容长度大于20，就显示前20个字，否则显示所有
                if(blog.getContent().length()>=20){
                    blog.setContent(blog.getContent().substring(0,20)+"...");
                }
                blog.setCreateTime(resultSet.getTimestamp(4));
                blog.setUserId(resultSet.getLong(5));
                //把blog对象加入集合中
                blogList.add(blog);
            }
            //返回结果
            return blogList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(statement,connection,resultSet);
        }
        return null;
    }

    /**
     * 根据博客Id查询对应的博客信息
     * @param id 博客Id
     * @return 一条博客记录
     */
    public Blog selectById(Long id){
        //非空校验
        if(id == null || id <= 0){
            return null;
        }
        //数据库操作
        //（1）定义数据库对象
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //（2）获取数据库连接
        try {
            connection = DBUtils.getConnection();
            //（3）定义sql语句
            String sql = "select id,title,content,createTime,userId from blog where id =?";
            //（4）预处理sql
            statement = connection.prepareStatement(sql);
            //（5）设置占位符
            statement.setLong(1,id);
            //（6）执行sql
            resultSet = statement.executeQuery();
            //（7）获取结果集
            if(resultSet.next()){
                //封装Blog对象
                Blog blog = new Blog();
                blog.setId(resultSet.getLong(1));
                blog.setTitle(resultSet.getString(2));
                blog.setContent(resultSet.getString(3));
                blog.setCreateTime(resultSet.getTimestamp(4));
                blog.setUserId(resultSet.getLong(5));
                //（8）返回结果
                return blog;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(statement,connection,resultSet);
        }
        return null;
    }

    /**
     * 发布一篇新的博客
     * @param blog Blog对象
     * @return 受影响的行数
     */
    public int insert(Blog blog){
        //1、非空校验
        if(blog == null || StringUtils.isEmpty(blog.getTitle())
                || StringUtils.isEmpty(blog.getContent())
                || blog.getUserId() == null
                || blog.getCreateTime() == null){
            return 0;
        }
        //数据库操作
        //(1)定义操作数据库过程中的变量
        Connection connection = null;
        PreparedStatement statement = null;
        //(2)创建数据库连接
        try {
            connection = DBUtils.getConnection();
            //(3)定义SQL语句
            String sql = "insert into blog values (null,?,?,?,?);";
            //(4)预处理sql
            statement = connection.prepareStatement(sql);
            //(5)设置占位符的值
            statement.setString(1,blog.getTitle());
            statement.setString(2,blog.getContent());
            statement.setTimestamp(3,blog.getCreateTime());
            statement.setLong(4,blog.getUserId());
            //(6)执行sql
            int row = statement.executeUpdate();
            //返回结果
            return row;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(statement,connection,null);
        }
        return 0;
    }

    /**
     * 删除博客
     * @param id 传入要删除的博客id
     * @return 返回受影响的行数
     */
    public int deleteById(Long id){
        //非空校验
        if(id == null || id == 0){
            return 0;
        }
        //(1)定义sql的对象
        Connection connection = null;
        PreparedStatement statement = null;
        //(2)定义数据库连接
        try {
            connection = DBUtils.getConnection();
            //(3)定义sql语句
            String sql = "delete from blog where id = ?";
            //(4)预处理sql
            statement = connection.prepareStatement(sql);
            //(5)设置占位符
            statement.setLong(1,id);
            //(6)执行sql
            int row = statement.executeUpdate();
            //(7)返回结果
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(statement,connection,null);
        }
        return 0;
    }
}
